﻿using System;
using System.Data;
using System.Collections.Generic;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class HRMJobSpecListDAL : IHRMJobSpecListDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public IList<PositionInfo> LoadPositionTreeView(Nullable<int> EmpTypeSeq)
        {
            IList<PositionInfo> infoList = new List<PositionInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                string strSQL = "SELECT pos.* FROM("
                                + " SELECT 0 AS POS_SEQ, NULL AS POS_REF"
                                + ", 'แบ่งตามกฏกระทรวง' AS POS_NAME"
                                + ", NULL AS POS_STATUS_NAME, NULL AS upper_pos_seq"
                                + ", 'แบ่งตามกฏกระทรวง' AS POS_TEXT, NULL AS JOB_DESCRIPTION"
                                + " UNION ALL"
                                + " SELECT p.POS_SEQ, p.POS_REF, CONCAT(p.POS_REF, ' - ', p.POS_NAME) AS POS_NAME"
                                + ", CASE WHEN e.POS_SEQ IS NULL THEN CAST('ว่าง' AS CHAR)"
                                + " ELSE 'มีคนครอง' END AS POS_STATUS_NAME"
                                + ", CASE WHEN ISNULL(p.upper_pos_seq) THEN 0 ELSE p.upper_pos_seq END AS upper_pos_seq"
                                + ", CAST(CONCAT(CAST(p.POS_REF AS CHAR), ' - '"
                                + ", CASE WHEN p.EXEC_POS_SEQ = 0 OR ISNULL(p.EXEC_POS_SEQ) THEN CAST(f.FIELD_POS_LINE AS CHAR)"
                                + " ELSE CAST(CONCAT(ex.EXEC_POS_NAME"
                                + ", CASE WHEN f.POS_TYPE_SEQ = 4 THEN '' "
                                + " ELSE j.JOB_SITE_NAME "
                                + "END) AS CHAR)"
                                + " END"
                                + ") AS CHAR) AS POS_TEXT"
                                + ", p.JOB_DESCRIPTION"
                                + " FROM HRM_MP_POSITIONS p"
                                + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                                + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                                + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                                + " LEFT JOIN HRM_PS_EMPLOYEES e ON p.POS_SEQ = e.POS_SEQ AND e.EMP_STATUS_SEQ = 1"
                                + " WHERE p.RECORD_STATUS = @ActiveRecord"
                                + " AND f.EMP_TYPE_SEQ = @EmpTypeSeq"
                                + ")pos "
                                + " ORDER BY pos.POS_REF + 0";

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                DataRowCollection dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PositionInfo info = new PositionInfo();
                        info.Sequence = Int32.Parse(dr[i]["POS_SEQ"].ToString());
                        info.RefNo = Convert.IsDBNull(dr[i]["POS_REF"]) ? "" : (dr[i]

                        ["POS_REF"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["POS_TEXT"]) ? "" : (dr[i]

                        ["POS_TEXT"]).ToString();
                        info.Parent = Convert.IsDBNull(dr[i]["upper_pos_seq"]) ? (int?)null : 

                        Int32.Parse(dr[i]["upper_pos_seq"].ToString());
                        info.JobDescription = Convert.IsDBNull(dr[i]["JOB_DESCRIPTION"]) ? "" : (dr[i]

                        ["JOB_DESCRIPTION"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<TreeViewInfo> LoadPositionJobsites(int? EmpTypeSeq, string ParentID)
        {
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@JobSiteTypeSeq", SystemInfo._JobsiteExternal);
                dbManager.AddParameters(2, "@JobSiteUpper", SystemInfo._JobsiteExternal);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@CancelRecord", SystemInfo._CancelRecordStatus);
                dbManager.AddParameters(5, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@TypeName", "โครงสร้างตามกฏกระทรวง");
                dbManager.AddParameters(7, "@parent_id", ParentID);

                string strSQL = "SELECT t.* FROM(" + "\n"
                                + "SELECT CAST(j.JOB_SITE_SEQ AS CHAR) AS ID, "
                                + " j.JOB_SITE_NAME AS NAME" + "\n"
                                + ", CASE WHEN l.JOB_SITE_LEVEL_NO > 2 THEN CAST(j.upper_job_site_seq AS CHAR)" + "\n"
                                + " ELSE NULL END AS PARENT_ID" + "\n"
                                + ", CASE l.JOB_SITE_LEVEL_NO " + "\n"
                                + " WHEN 1 THEN '../Images/Buttons/xx.gif'" + "\n"
                                + " WHEN 2 THEN '../Images/Buttons/xx.gif'" + "\n"
                                + " WHEN 3 THEN '../Images/Buttons/box_green.gif'" + "\n"
                                + " WHEN 4 THEN '../Images/Buttons/box_blue.gif'" + "\n"
                                + " WHEN 5 THEN '../Images/Buttons/box_pink.gif'" + "\n"
                                + " ELSE '' END AS IMAGE_URL" + "\n"
                                + ",  (select count(pos_seq) from hrm_mp_positions" + "\n"
                                + " where record_status = @ActiveRecord" + "\n"
                                + " and emp_type_seq = @EmpTypeSeq" + "\n"
                                + " and job_site_seq = j.job_site_seq)" + "\n"
                                + " + (select count(job_site_seq) from hrm_mp_job_sites" + "\n"
                                + " where record_status = @ActiveRecord" + "\n"
                                + " and upper_job_site_seq = j.job_site_seq) " + "\n"
                                + "  AS CHILDREN_COUNT" + "\n"
                                + " , l.job_site_level_name AS descr " + "\n"
                                + " FROM HRM_MP_JOB_SITES j" + "\n"
                                + " LEFT JOIN HRM_MP_JOB_SITE_LEVELS l ON j.JOB_SITE_LEVEL_SEQ = l.JOB_SITE_LEVEL_SEQ" + "\n"
                                + " WHERE j.RECORD_STATUS NOT IN (@DeleteRecord, @CancelRecord)" 
                                + "\n"
                                + " AND j.JOB_SITE_FULL_PATH LIKE (select concat('%/', curr_job_site_seq, '/%') from hrm_mp_job_site_types " + "\n"
                                + " where job_site_type_seq = @JobSiteTypeSeq)" + "\n"
                                + " AND l.JOB_SITE_LEVEL_NO > 1" + "\n"
                                + " UNION ALL" + "\n"
                                + " SELECT CAST(CONCAT('1', '-', POS_SEQ) AS CHAR) AS ID" + "\n"
                                + ", CONCAT(POS_REF, ' : ', POS_NAME) AS NAME" + "\n"
                                + ", CAST(JOB_SITE_SEQ AS CHAR) AS PARENT_ID" + "\n"
                                + ", '../Images/Buttons/box_orange.gif' AS IMAGE_URL" + "\n"
                                + ", 0 AS CHILDREN_COUNT" + "\n"
                                + ", IF(HRM_MP_POSITIONS.POS_STATUS = 1, 'ว่าง', 'มีคนครอง') AS descr " 
                                + "\n"
                                + " FROM HRM_MP_POSITIONS" + "\n"
                                + " where HRM_MP_POSITIONS.record_status = @ActiveRecord "
                                + " and  EMP_TYPE_SEQ = @EmpTypeSeq" + "\n"
                                + ")t" + "\n"
                                //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                                + (string.IsNullOrEmpty(ParentID) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                DataRowCollection dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();

                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]

                        ["PARENT_ID"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]

                        ["IMAGE_URL"]).ToString();
                        info.Descr = Convert.ToString(dr[i]["descr"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList; 
        }

        public void Insert(string strPosSeq, string strSpecDetail, int intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@pos_seq", strPosSeq);
                dbManager.AddParameters(1, "@spec_detail", strSpecDetail);
                dbManager.AddParameters(2, "@created_by", intCreateBy);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);

                strSQL = "INSERT INTO hrm_perf_job_specification"
                         + " (pos_seq, spec_detail, created_when, created_by, record_status) "
                         + " VALUES(@pos_seq, @spec_detail, GETDATE(), @created_by, @record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Search(string intJobSpacSeq, string strPosSeq, string strSpecDetail)
         {
             string strSQL;

             try
             {
                 dbManager.Open();
                 dbManager.CreateParameters(4);

                 dbManager.AddParameters(0, "@job_spec_seq", intJobSpacSeq);
                 dbManager.AddParameters(1, "@pos_seq", strPosSeq);
                 dbManager.AddParameters(2, "@spec_detail", strSpecDetail);
                 dbManager.AddParameters(3, "@status", SystemInfo._DeleteRecordStatus);

                 strSQL = " SELECT job_spec_seq, pos_seq, spec_detail FROM hrm_perf_job_specification"
                         + " WHERE pos_seq = @pos_seq AND record_status not like @status";

                 DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                 return dtb;

             }
             catch (Exception ex)
             {
                 throw ex;
             }
             finally
             {
                 dbManager.Dispose(); 
             }
         }

        public void Update(string strSpecDetail, string intJobSpacSeq, int intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0,"@job_spec_seq",intJobSpacSeq);
                dbManager.AddParameters(1,"@spec_detail", strSpecDetail);
                dbManager.AddParameters(2,"@updated_by", intCreateBy);
                dbManager.AddParameters(3,"@status", SystemInfo._DeleteRecordStatus);

                strSQL = " UPDATE hrm_perf_job_specification "
                         + " SET spec_detail = @spec_detail, "
                         + " updated_when = GETDATE(), "
                         + " updated_by = @updated_by"
                         + " WHERE job_spec_seq = @job_spec_seq AND record_status not like @status ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
                catch (Exception ex)
            {
                throw ex;
            }
                finally
            {
                dbManager.Dispose(); 
            }
        }

        public void Delete(string intJobSpacSeq, int intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@job_spec_seq", intJobSpacSeq);
                dbManager.AddParameters(1, "@updated_by", intCreateBy);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " UPDATE hrm_perf_job_specification "
                         + " SET record_status = @status,"
                         + " updated_by = @updated_by,"
                         + " updated_when = GETDATE()"
                         + " WHERE job_spec_seq = @job_spec_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
                finally
            {
                dbManager.Dispose();
            }
        }

        public bool CheckValueSubmit(string intJobSpacSeq)
        {
            string strSQL;

            bool result = true;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@job_spec_seq", intJobSpacSeq );
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT job_spec_seq, pos_seq, spec_detail, created_by FROM hrm_perf_job_specification"
                         +" WHERE record_status = @status";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return result;
        }

        public string LoadNamePos(string strPosSeq)
        {
            string strSQL = "";
            string LoadNamePos = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "pos_seq", strPosSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT pos_name FROM hrm_mp_positions"
                        + " WHERE pos_seq = @pos_seq ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                LoadNamePos = Convert.ToString(dtb.Rows[0][0]);
                return LoadNamePos;
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}